﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;

namespace TienTeTuong
{
    public class Order
    {
        public bool saveOrder(OrderDTO Order, string userID)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;
            var lstProDetail = new List<CateDetailProductDTO>();
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySql.Data.MySqlClient.MySqlCommand();
                MysqlCmd.Connection = SqlConnection;
                Order.Total = 0;
                foreach (var item in Order.Items)
                {
                    foreach (var product in item.Products)
                    {
                        Order.Total += Convert.ToDouble(product.Price) * item.CategoryQuantity;
                        MysqlCmd.CommandText = "INSERT INTO `orderdetail`(`OrderID`, `CategoryDetailID`, `ProductID`, `ProName`, `Quantity`, `NameToDisplay`, `Price`, `Unit`, `CateQuantity`)" +
                        "VALUES ('" + Order.ID + "','" + item.CategoryDetailID + "','" + product.ProId + "','" + product.ProName + "','" + item.CategoryQuantity + "','" + item.NameToDisplay + "','" + product.Price + "','" + product.unit + "','" + item.CategoryQuantity.ToString() + "')";
                        MysqlCmd.ExecuteNonQuery();
                    }
                }
                MysqlCmd.CommandText =
                    "INSERT INTO `Orders`(`OrderID`, `UserID`, `ReceiverName`, `Email`, `Address`, `Phone`, `Note`, `Status`, `Total`)" +
                    " VALUES ('" + Order.ID + "','" + userID + "','" + Order.ReceiverName + "','" + Order.Email + "','" + Order.Address + "','"
                    + Order.Phone + "','" + Order.Note + "','" + Order.Status + "','" + Order.Total + "')";
                MysqlCmd.ExecuteNonQuery();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public List<ProductInOrder> getOrderDetailByID(string orderID)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;
            var lstProDetail = new List<ProductInOrder>();
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };


                var strSQL = @"SELECT * FROM `orderdetail` where `orderdetail`.`OrderID` = '" + orderID + "' order by `orderdetail`.`CategoryDetailID`";
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteReader();

                var cateDetail = new ProductInOrder();
                int tmpQuantity = 0;
                double tmpPrice = 0;

                while (reader.Read())
                {
                    try
                    {
                        if (cateDetail.CategoryDetailID.Equals(reader[1].ToString()))
                        {
                            var proDetail = new CateDetailProductDTO();
                            proDetail.Price = reader[5].ToString();
                            proDetail.ProName = reader[8].ToString();
                            proDetail.ProId = reader[2].ToString();
                            float tmpD;
                            float.TryParse(reader[3].ToString(), out tmpD);
                            proDetail.Quantity = tmpD;
                            proDetail.unit = reader[9].ToString();
                             int tmpDs;
                            int.TryParse(reader[10].ToString(), out tmpDs);
                            cateDetail.CategoryQuantity = tmpDs;
                            cateDetail.Price += int.Parse(proDetail.Price) * tmpD * cateDetail.CategoryQuantity;
                            cateDetail.Products.Add(proDetail);
                        }
                        else if (String.IsNullOrEmpty(cateDetail.CategoryDetailID))
                        {

                            cateDetail.CategoryDetailID = reader[1].ToString();
                            cateDetail.NameToDisplay = reader[4].ToString();
                            int.TryParse(reader[3].ToString(), out tmpQuantity);
                            //cateDetail.CategoryQuantity = tmpQuantity;
                            cateDetail.Price += Convert.ToDouble(reader[5].ToString());

                            var proDetail = new CateDetailProductDTO();
                            proDetail.Price = reader[5].ToString();
                            proDetail.ProName = reader[8].ToString();
                            proDetail.ProId = reader[2].ToString();
                            double tmpD;
                            double.TryParse(reader[3].ToString(), out tmpD);
                            proDetail.Quantity = tmpD;
                            int tmpDs;
                            int.TryParse(reader[10].ToString(), out tmpDs);
                            cateDetail.CategoryQuantity = tmpDs;
                            proDetail.unit = reader[9].ToString();
                            cateDetail.Price = int.Parse(proDetail.Price) * tmpD * cateDetail.CategoryQuantity;
                            cateDetail.Products.Add(proDetail);

                        }
                        else if (!cateDetail.CategoryDetailID.Equals(reader[1].ToString()))
                        {
                            lstProDetail.Add(cateDetail);
                            cateDetail = new ProductInOrder();

                            cateDetail.CategoryDetailID = reader[1].ToString();
                            cateDetail.NameToDisplay = reader[4].ToString();
                            int.TryParse(reader[3].ToString(), out tmpQuantity);
                            double.TryParse(reader[5].ToString(), out tmpPrice);
                            cateDetail.CategoryQuantity = tmpQuantity;
                            cateDetail.Price += tmpPrice;

                            var proDetail = new CateDetailProductDTO();
                            proDetail.Price = reader[5].ToString();
                            proDetail.ProId = reader[2].ToString();
                            proDetail.ProName = reader[8].ToString();
                            double tmpD;
                            double.TryParse(reader[3].ToString(), out tmpD);
                            proDetail.Quantity = tmpD;
                            proDetail.unit = reader[9].ToString();
                            int tmpDs;
                            int.TryParse(reader[10].ToString(), out tmpDs);
                            cateDetail.CategoryQuantity = tmpDs;
                            cateDetail.Price = int.Parse(proDetail.Price) * tmpD * cateDetail.CategoryQuantity;
                            cateDetail.Products.Add(proDetail);
                        }
                    }
                    catch (Exception ex)
                    {
                        Logger.WriteLog(LogLevel.ERROR, ex + "\n" + ex.StackTrace);
                    }
                }

                if (!String.IsNullOrEmpty(cateDetail.CategoryDetailID))
                {
                    lstProDetail.Add(cateDetail);
                }

                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return lstProDetail;

            }
            catch (Exception ex)
            {
                if (reader != null) reader.Close();
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return lstProDetail;
            }
        }

        public List<OrderDTO> listOrder()
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;
            var lstProDetail = new List<OrderDTO>();
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                //  var strSQL = @"SELECT a.OptionID,a.ProID,b.Name,b.Price,a.Quantity FROM CateDetailProduct AS a JOIN Productdetail AS b ON a.ProID = b.ProID Where OptionID='" + optionID + "'";
                var strSQL = @"SELECT * FROM `Orders` order by `Orders`.`Createddate` DESC";
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    try
                    {
                        var order = new OrderDTO();
                        order.ID = reader[0].ToString();
                        order.UserID = reader[1].ToString();
                        order.ReceiverName = reader[4].ToString();
                        order.Email = reader[5].ToString();
                        order.Address = reader[6].ToString();
                        order.Phone = reader[7].ToString();
                        order.Note = reader[8].ToString();
                        order.Status = reader[9].ToString();
                        order.CreatedDate = Convert.ToString(reader[2]);
                        order.UpdatedDate = Convert.ToString(reader[3]);
                        order.Total = Convert.ToDouble(reader[10].ToString());
                        order.AdminNote = Convert.ToString(reader[11].ToString());
                        order.Items = getOrderDetailByID(order.ID);
                        lstProDetail.Add(order);
                    }
                    catch (Exception ex)
                    {
                        Logger.WriteLog(LogLevel.ERROR, ex + "\n" + ex.StackTrace);
                    }

                }
                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return lstProDetail;

            }
            catch (Exception ex)
            {
                if (reader != null) reader.Close();
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return lstProDetail;
            }
        }

        public bool changeStatus(string adminNote, string status, string orderid)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            int reader = 0;
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                //  var strSQL = @"SELECT a.OptionID,a.ProID,b.Name,b.Price,a.Quantity FROM CateDetailProduct AS a JOIN Productdetail AS b ON a.ProID = b.ProID Where OptionID='" + optionID + "'";
                var strSQL = @"UPDATE `Orders` SET  `Status` = '" + status + "', `AdNote` = '" + adminNote + "'  WHERE  `Orders`.`OrderID` =  '" + orderid + "';";
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteNonQuery();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return reader > 0;
            }
            catch (Exception ex)
            {
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return false;
            }
        }

        public bool delete(string orderid)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            int reader = 0;
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                //  var strSQL = @"SELECT a.OptionID,a.ProID,b.Name,b.Price,a.Quantity FROM CateDetailProduct AS a JOIN Productdetail AS b ON a.ProID = b.ProID Where OptionID='" + optionID + "'";
                var strSQL = @"Delete from `Orders`  WHERE  `Orders`.`OrderID` =  '" + orderid + "';";
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteNonQuery();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return reader > 0;
            }
            catch (Exception ex)
            {
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return false;
            }
        }

    }
}